package com.dk.common.dbaccess.interceptor;

import com.google.common.cache.Cache;
import com.google.common.cache.CacheBuilder;
import lombok.extern.slf4j.Slf4j;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.LongValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.delete.Delete;
import net.sf.jsqlparser.statement.select.FromItem;
import net.sf.jsqlparser.statement.select.Join;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.update.Update;
import net.sf.jsqlparser.statement.update.UpdateSet;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Locale;
import java.util.concurrent.ConcurrentHashMap;
import java.util.concurrent.ConcurrentMap;
import java.util.concurrent.ExecutionException;
import java.util.concurrent.TimeUnit;

@Slf4j
public class SoftDeleteSqlParser {
    private SoftDeleteSqlParser() {
    }

    private static final Cache<String, String> SQL_CACHE = CacheBuilder.newBuilder()
            .expireAfterAccess(1, TimeUnit.HOURS)
            .maximumSize(10000)
            .build();

    // 缓存表结构信息（表名 -> 是否包含 deleted 字段）
    private static final ConcurrentMap<String, Boolean> TABLE_DELETED_COLUMN_CACHE =
            new ConcurrentHashMap<>();

    public static String addSoftDeleteCondition(String sql, Connection connection) {
        try {
            return SQL_CACHE.get(sql, () -> parseAndModifySql(sql, connection));
        } catch (ExecutionException e) {
            log.error("Error parsing SQL: {}", e.getMessage());
            return sql;
        }
    }

    public static String parseAndModifySql(String sql, Connection connection) {
        try {
            Statement statement = CCJSqlParserUtil.parse(sql);

            // 处理 SELECT 语句
            if (statement instanceof Select select) {
                return processSelectStatement(select, connection);
            }
            // 处理 DELETE 语句
            if (statement instanceof Delete delete) {
                return processDeleteStatement(delete, connection);
            }

            return sql;
        } catch (Exception e) {
            log.warn("Failed to parse SQL: {}, error: {}", sql, e.getMessage());
            return sql;
        }
    }

    // ==================== SELECT 处理 ====================
    private static String processSelectStatement(Select select, Connection connection) throws SQLException {
        PlainSelect plainSelect = select.getPlainSelect();
        if (plainSelect == null) return select.toString();

        List<TableInfo> tables = extractTables(plainSelect);
        List<TableInfo> tablesWithDeleted = filterTablesWithDeleted(tables, connection);
        if (tablesWithDeleted.isEmpty()) return select.toString();

        Expression deletedCondition = createDeletedCondition(tablesWithDeleted);
        addConditionToWhere(plainSelect, deletedCondition);

        return select.toString();
    }

    // ==================== DELETE 处理 ====================
    private static String processDeleteStatement(Delete delete, Connection connection) throws SQLException {
        Table table = delete.getTable();
        String tableName = extractRealTableName(table.getName());

        // 检查表是否有 deleted 列
        if (!hasDeletedColumn(tableName, connection)) {
            log.debug("Table {} has no deleted column, executing physical delete", tableName);
            return delete.toString();
        }

        // 创建 UPDATE 语句
        Update update = new Update();
        update.setTable(table);

        // 设置 deleted = 1
        List<UpdateSet> updateSets = new ArrayList<>();
        updateSets.add(new UpdateSet(
                new Column("deleted"),
                new LongValue(1)
        ));
        update.setUpdateSets(updateSets);

        // 保留原 WHERE 条件
        Expression where = delete.getWhere();

        // 添加 deleted = 0 条件
        String tableRef = table.getAlias() != null ?
                table.getAlias().getName() :
                table.getName();
        Expression deletedCondition = new EqualsTo(
                new Column(tableRef + ".deleted"),
                new LongValue(0)
        );

        if (where != null) {
            update.setWhere(new AndExpression(where, deletedCondition));
        } else {
            update.setWhere(deletedCondition);
        }

        return update.toString();
    }

    // ==================== 通用工具方法 ====================
    private static List<TableInfo> extractTables(PlainSelect plainSelect) {
        List<TableInfo> tables = new ArrayList<>();

        // 主表
        FromItem fromItem = plainSelect.getFromItem();
        if (fromItem instanceof Table table) {
            tables.add(new TableInfo(table));
        }

        // JOIN 表
        List<Join> joins = plainSelect.getJoins();
        if (joins != null) {
            for (Join join : joins) {
                FromItem joinItem = join.getRightItem();
                if (joinItem instanceof Table table) {
                    tables.add(new TableInfo(table));
                }
            }
        }

        return tables;
    }

    private static void addConditionToWhere(PlainSelect plainSelect, Expression condition) {
        if (condition == null) return;

        Expression where = plainSelect.getWhere();
        if (where != null) {
            plainSelect.setWhere(new AndExpression(where, condition));
        } else {
            plainSelect.setWhere(condition);
        }
    }

    private static Expression createDeletedCondition(List<TableInfo> tables) {
        if (tables.isEmpty()) {
            return null;
        }

        // 单个表条件
        if (tables.size() == 1) {
            TableInfo table = tables.getFirst();
            return createTableCondition(table);
        }

        // 多个表条件（使用 AND 连接）
        Expression finalCondition = null;
        for (TableInfo table : tables) {
            Expression condition = createTableCondition(table);
            if (finalCondition == null) {
                finalCondition = condition;
            } else {
                finalCondition = new AndExpression(finalCondition, condition);
            }
        }
        return finalCondition;
    }

    private static Expression createTableCondition(TableInfo tableInfo) {
        String tableRef = tableInfo.alias != null ?
                tableInfo.alias :
                tableInfo.table.getName();

        return new EqualsTo(
                new Column(tableRef + ".deleted"),
                new LongValue(0)
        );
    }

    /**
     * 过滤出包含 deleted 字段的表
     */
    private static List<TableInfo> filterTablesWithDeleted(List<TableInfo> tables, Connection connection) {
        List<TableInfo> result = new ArrayList<>();
        for (TableInfo tableInfo : tables) {
            String tableName = extractRealTableName(tableInfo.table.getName());
            if (hasDeletedColumn(tableName, connection)) {
                result.add(tableInfo);
            }
        }
        return result;
    }

    /**
     * 检查表是否包含 deleted 字段（带缓存）
     */
    private static boolean hasDeletedColumn(String tableName, Connection connection) {
        return TABLE_DELETED_COLUMN_CACHE.computeIfAbsent(tableName, key ->
                hasColumn(connection, tableName, "deleted")
        );
    }

    /**
     * 从表引用中提取真实表名
     */
    private static String extractRealTableName(String tableRef) {
        // 处理带别名的表引用: "users u" -> "users"
        if (tableRef.contains(" ")) {
            return tableRef.substring(0, tableRef.indexOf(' '));
        }

        // 处理带模式名的表引用: "public.users" -> "users"
        if (tableRef.contains(".")) {
            return tableRef.substring(tableRef.lastIndexOf('.') + 1);
        }

        return tableRef;
    }

    public static boolean hasColumn(Connection conn, String tableName, String columnName) {
        String normalizedTable = tableName.toLowerCase(Locale.ENGLISH);
        String normalizedColumn = columnName.toLowerCase(Locale.ENGLISH);

        try {
            DatabaseMetaData metaData = conn.getMetaData();
            String schema = conn.getSchema();

            // 特殊处理Oracle
            if (metaData.getDatabaseProductName().contains("Oracle")) {
                schema = metaData.getUserName();
            }

            try (ResultSet rs = metaData.getColumns(null, schema, normalizedTable, normalizedColumn)) {
                return rs.next();
            }
        } catch (SQLException e) {
            log.warn("Error checking column existence: {}.{} - {}",
                    tableName, columnName, e.getMessage());
            return false;
        }
    }

    private static class TableInfo {
        final Table table;
        final String alias;

        TableInfo(Table table) {
            this.table = table;
            this.alias = table.getAlias() != null ?
                    table.getAlias().getName() :
                    null;
        }
    }
}